﻿/*
Commerce by Onex – eCommerce software platform (www.ecommerce.byonex.com)
Copyright (C) 2008-2010 by Onex Group

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 
 */
using System;
using System.Collections.Generic;
using System.Text;

using ShopModel;
using System.Data.SqlClient;
using System.Data;
using System.Text.RegularExpressions;

namespace ShopDAO
{
    public class CommentDAO
    {
        private string connectionString;

        public CommentDAO()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["csShop"].ConnectionString;
        }

        private SqlConnection Connection()
        {
            return new SqlConnection(connectionString);
        }

        /// <summary>
        /// Pobiera komentarze dla produktu.(Downloads comments for a product.)
        /// </summary>
        /// <param name="productId">Unikalny Id produktu.(Unique ID product.)</param>
        /// <param name="hidden">Status komentarza ukryty.(Hidden comment’s status.)</param>
        /// <returns>Zwraca liste obiektow klasy Comment.(Returns list of Comment object.)</returns>
        public List<Comment> GetCommentForProduct(int productId, bool hidden)
        {
            List<Comment> result = new List<Comment>();
            SqlConnection connection = Connection();

            SqlCommand command = connection.CreateCommand();
            command.CommandText = "GetCommentForProduct";
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new SqlParameter("@product_id", productId));
            command.Parameters.Add(new SqlParameter("@hidden", hidden));
            try
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                while (reader.Read())
                {
                    result.Add(new Comment((int)reader["IDcomment"], (string)reader["Author"], (string)reader["Content"], (DateTime)reader["Date"], (reader["Rate"] is DBNull ? -1 : (decimal)reader["Rate"]),(bool)reader["Accepted"],(bool)reader["Hidden"]));
                }

                reader.Close();
                return result;
            }
            catch (Exception ex)
            {
                ErrorLogger.LogError(ex);
                connection.Close();
                return new List<Comment>();
            }
        }

        /// <summary>
        /// Dodaje komentarz do produktu.(Adds comment to the product.)
        /// </summary>
        /// <param name="comment">Obiekt klasy Comment.(Comment class object.)</param>
        /// <param name="productId">Unikalny Id produktu.(Unique ID product.)</param>
        /// <returns>Zwraca prawde w przypadku poprawnego dodania komentarza.(Returns ‘truth’ when comment successfully added.)</returns>
        public bool AddCommentToProduct(Comment comment, int productId)
        {
            SqlConnection connection = Connection();
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "AddCommentToProduct";
            command.CommandType = CommandType.StoredProcedure;
            
            command.Parameters.Add(new SqlParameter("@author", comment.Author));
            command.Parameters.Add(new SqlParameter("@content", comment.Content));
            command.Parameters.Add(new SqlParameter("@product_id", productId));
            command.Parameters.Add(new SqlParameter("@date", comment.Date));
            if(comment.Rate!=-1)
            command.Parameters.Add(new SqlParameter("@rate", comment.Rate));
            else
                command.Parameters.Add(new SqlParameter("@rate", DBNull.Value));

            try
            {
                connection.Open();
                command.ExecuteNonQuery();

                return true;
            }
            catch(Exception ex)
            {
                ErrorLogger.LogError(ex);
                return false;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Pobiera srednia ocene danego produktu.(Downloads average mark of given product.)
        /// </summary>
        /// <param name="_ProductID">Unikalny Id produktu.(Unique ID product.)</param>
        /// <returns>Zwraca obiekt typu decimal.(Returns decimal object)</returns>
        public decimal GetAvgRateForProduct(int _ProductID)
        {
            SqlConnection connection = Connection();
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "GetAvgRateForProduct";
            command.CommandType = CommandType.StoredProcedure;
            decimal result = 0;
            command.Parameters.Add(new SqlParameter("@product_id", _ProductID));

            try
            {
                connection.Open();
                
               result = Convert.ToDecimal(command.ExecuteScalar());
               return result;
            }
            catch(Exception ex)
            {
                ErrorLogger.LogError(ex);
                return 0;
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Pobiera produkty posortowane wedlug daty dodania komentarza.(Downloads products sorted according to date of comment addition.)
        /// </summary>
        /// <param name="LanguageID">Unikalny Id jezyka.(Unique ID of the Language.)</param>
        /// <returns>Zwraca liste obiektow klasy CommentStatistic.(Returns List of CommentStatistic objects.)</returns>
        public List<CommentStatistics> GetProductsSortedByCommentsDate(int? LanguageID)
        {
            List<CommentStatistics> result = new List<CommentStatistics>();
            SqlConnection connection = Connection();
            CommentStatistics product;

            SqlCommand command = connection.CreateCommand();
            command.CommandText = "GetProductsSortedByCommentsDate";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@LanguageID", LanguageID));

            try
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                while (reader.Read())
                {
                    product = new CommentStatistics();
                    product.ProductID = (int)reader["IDProduct"];
                    product.ProductName = (string)reader["ProductName"];
                    product.Count = (int)reader["Count"];
                    product.Hidden = (int)reader["Hidden"];
                    product.Accepted = (int)reader["Accepted"];
                    product.ToAcceptation = (int)reader["ToAcceptation"];
                    result.Add(product);
                }

                reader.Close();
                return result;
            }
            catch(Exception ex)
            {
                ErrorLogger.LogError(ex);
                connection.Close();
                return null;
            }

        }
        /// <summary>
        /// Uaktualnianie status komentarza.(Updates comment’s status.)
        /// </summary>
        /// <param name="productID">Unikalny Id produktu.(Unique ID of the product.)</param>
        /// <param name="acceptList">status komentarza akceptowany.(acceptable comment’s status.)</param>
        /// <param name="unacceptList">status komentarza nieakceptowany.(Unacceptable comment’s status.)</param>
        /// <param name="hideList">status komentarza ukryty.(Hidden comment’s status.)</param>
        /// <param name="unhideList">status komentarza widoczny.(Visible comment’s status.)</param>
        /// <returns>Zwraca prawde w przypadku poprawnego uaktualniania statusu komentarza.(Returns ‘truth’ when comment’s status update succeeded.)</returns>
        public bool UpdateCommentsState(int productID, string acceptList, string unacceptList, string hideList, string unhideList)
        {
            SqlConnection connection = Connection();
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "UpdateCommentsState";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new SqlParameter("@productID", productID));
            command.Parameters.Add(new SqlParameter("@acceptList", acceptList));
            command.Parameters.Add(new SqlParameter("@unacceptList", unacceptList));
            command.Parameters.Add(new SqlParameter("@hideList", hideList));
            command.Parameters.Add(new SqlParameter("@unhideList", unhideList));
            try
            {
                connection.Open();
                command.ExecuteNonQuery();
                return true;
            }
            catch(Exception ex)
            {
                ErrorLogger.LogError(ex);
                return false;
            }
            finally
            {
                connection.Close();
            }

        }
    }
}
